﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class UserDiarytbDAO
    {
        //保存日记信息
        public int saveUserDiary(string diaryTitle, string uln, string diaryContent, DateTime sdt)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into UserDiarytb values(@diaryTitle, @uln, @diaryContent, @sdt)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@diaryTitle", diaryTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@uln", uln));
                sqlCmd.Parameters.Add(new SqlParameter("@diaryContent", diaryContent));
                sqlCmd.Parameters.Add(new SqlParameter("@sdt", sdt));
                sqlCmd.ExecuteNonQuery();
                sql = "select @@identity";
                sqlCmd = new SqlCommand(sql, sqlConn);
                count = int.Parse(sqlCmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        //根据日记编号查找日记信息
        public UserDiary findByDiaryId(int did)
        {
            UserDiary userDiary = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserDiarytb where DiaryId = @did";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@did", did));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr != null && sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    userDiary = new UserDiary();
                    sqlDr.Read();
                    userDiary.DiaryId = (int)sqlDr["DiaryId"];
                    userDiary.DiaryTitle = sqlDr["DiaryTitle"].ToString();
                    userDiary.DiaryUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                    userDiary.DiaryContent = sqlDr["DiaryContent"].ToString();
                    userDiary.RegisterTime = (DateTime)sqlDr["RegisterTime"];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return userDiary;
        }

        //根据用户登录名查找日记信息
        public List<UserDiary> findByUserLoginName(string uln)
        {
            List<UserDiary> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserDiarytb where UserLoginName = @uln order by RegisterTime desc";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@uln", uln));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr != null && sqlDr.HasRows)
                {
                    list = new List<UserDiary>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        UserDiary userDiary = new UserDiary();
                        userDiary.DiaryId = (int)sqlDr["DiaryId"];
                        userDiary.DiaryTitle = sqlDr["DiaryTitle"].ToString();
                        userDiary.DiaryUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                        userDiary.DiaryContent = sqlDr["DiaryContent"].ToString();
                        userDiary.RegisterTime = (DateTime)sqlDr["RegisterTime"];
                        list.Add(userDiary);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据日记编号删除日记信息
        public int delDiaryByDiaryId(int did)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from UserDiarytb where DiaryId = @did";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@did", did));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        //查找指定日期内的日记信息
        public List<UserDiary> findRangeOfDate(string uln, DateTime start, DateTime end)
        {
            List<UserDiary> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserDiarytb where where UserLoginName = @uln and RegisterTime > @start and RegisterTime < @end order by RegisterTime desc";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@uln", uln));
                sqlCmd.Parameters.Add(new SqlParameter("@start", start));
                sqlCmd.Parameters.Add(new SqlParameter("@end", end));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr != null && sqlDr.HasRows)
                {
                    list = new List<UserDiary>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        UserDiary userDiary = new UserDiary();
                        userDiary.DiaryId = (int)sqlDr["DiaryId"];
                        userDiary.DiaryTitle = sqlDr["DiaryTitle"].ToString();
                        userDiary.DiaryUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                        userDiary.DiaryContent = sqlDr["DiaryContent"].ToString();
                        userDiary.RegisterTime = (DateTime)sqlDr["RegisterTime"];
                        list.Add(userDiary);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 员工工作计划查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<UserDiary> findUserDiaryManager(string sql) {
            List<UserDiary> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlDr = sqlCmd.ExecuteReader();
                if(sqlDr.HasRows) {
                    list = new List<UserDiary>();
                    while(sqlDr.Read()) {
                        UserDiary userDiary = new UserDiary();
                        userDiary.DiaryId = (int) sqlDr["DiaryId"];
                        userDiary.DiaryTitle = sqlDr["DiaryTitle"].ToString();
                        userDiary.DiaryContent = sqlDr["DiaryContent"].ToString();
                        userDiary.RegisterTime = (DateTime) sqlDr["RegisterTime"];
                        list.Add(userDiary);
                    }
                }
            } catch(Exception ex) {
                Console.WriteLine(ex.Message);
            } finally {
                if(sqlDr != null) {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open) {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
